package com.example.board.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.board.pojo.Dept;
import com.example.board.pojo.dto.UserDto;
import com.example.board.pojo.vo.CityOrder;
import com.example.board.pojo.vo.CityOrderMoth;
import com.example.board.pojo.vo.CityRate;
import com.example.board.pojo.vo.NumberAmount;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface DistributorMapper extends BaseMapper<Dept> {
    @Select({
            "SELECT count(o.id) as orders ",
            "FROM sd_order o ",
            "where  o.order_state in(2,3,12)   and  o.on_cancel = 0 "
    })
    Integer findAllCityOrders();

    @Select({
            "SELECT count(su.id) as customer  ",
            "FROM sd_user_record su  ",
            "where su.deleted_at is null "
    })
    Integer findAllCityCustomer();

    @Select({
            "<script>",

            "SELECT count(o.id) as areaValue , sd.city as  city ",
            "from sd_order o ,sd_tool_man_info sd ",
            "<where> 1=1 ",
            "<if test='region != null and region != \"\" '> ",
            "and sd.city = #{region} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sd.province = #{province} ",
            "</if> ",

            " and ",
            "o.tool_man_id = sd.user_id ",
            "and o.order_time BETWEEN #{mothStart} and #{mothEnd} and o.on_cancel = 0  ",
            "</where>",
            "GROUP BY  sd.city",

            "</script>"

    })
    List<CityOrder> getCityOrder(@Param("province") String province,
                                 @Param("region") String region,
                                 @Param("mothStart") LocalDateTime mothStart,
                                 @Param("mothEnd") LocalDateTime mothEnd);


    @Select({
            "<script>",
            " SELECT ",
            " DATE_FORMAT( order_time, '%Y-%m' ) AS name, ",
            "  count(o.id) as data ",
            "FROM ",
            " sd_order o,sd_tool_man_info sd ,sys_user u ,sys_dept t ",
            "<where> 1=1 and sd.user_id = u.id and u.dept_id = t.id ",

            "<if test='city != null and city != \"\" '> ",
            "and sd.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sd.province = #{province} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and t.name = #{teamName} ",
            "</if> ",


            " and ",

            "  o.order_state in(2,3,12)   and  o.on_cancel = 0  and sd.user_id = o.tool_man_id ",
            "and o.order_time between #{startDate} and #{endDate}",

            "</where>",
            "GROUP BY ",
            " DATE_FORMAT( order_time, '%Y-%m' ) ",
            "ORDER BY ",
            " DATE_FORMAT( order_time, '%Y-%m' ) ASC ",

            "</script>"
    })
    List<CityOrderMoth> findCityOrderRate(@Param("province") String province,
                                          @Param("city") String city,
                                          @Param("startDate") LocalDateTime startDate,
                                          @Param("endDate") LocalDateTime endDate,
                                          @Param("teamName") String teamName);


    @Select({
            "<script>",
            "SELECT  count(o.id) as  suss  ",
            "from sd_order o ,sd_tool_man_info sd ,sys_user u ,sys_dept t ",

            "<where> 1=1 and sd.user_id = u.id and u.dept_id = t.id ",

            "<if test='city != null and city != \"\" '> ",
            "and sd.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sd.province = #{province} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and t.name = #{teamName} ",
            "</if> ",

            " and ",

            "o.tool_man_id = sd.user_id ",
            "and o.order_time between #{startDate} and #{endDate}",
            "</where> ",
            "</script> "
    })
    Integer findCityOrders(@Param("province") String province,
                           @Param("city") String city,
                           @Param("startDate") LocalDateTime startDate,
                           @Param("endDate") LocalDateTime endDate,
                           @Param("teamName") String teamName);

    @Select({
            "<script> ",
            "SELECT  ",
            " count(o.id)  as suss  ",
            "from sd_order o ,sd_tool_man_info sd ,sys_user u ,sys_dept t ",
            "<where> 1=1 and sd.user_id = u.id and u.dept_id = t.id ",

            "<if test='city != null and city != \"\" '> ",
            "and sd.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sd.province = #{province} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and t.name = #{teamName} ",
            "</if> ",


            " and ",

            "o.tool_man_id = sd.user_id ",
            " and o.on_cancel != 0 ",
            "and o.order_time between #{startDate} and #{endDate} ",
            "</where> ",
            "</script> "
    })
    Integer findCityOrdersFalse(@Param("province") String province,
                                @Param("city") String city,
                                @Param("startDate") LocalDateTime startDate,
                                @Param("endDate") LocalDateTime endDate,
                                @Param("teamName") String teamName);


    @Select({
            "<script> ",
            "SELECT  ",
            "o.user_record_id userRecordId , ",
            "count(o.id) as totalOrder ",
            "from sd_order o  ",
            "<where> 1=1 ",

            "<if test='province != null and province != \"\" '> ",
            "and o.receiver_province = #{province} ",
            "</if> ",

            "<if test='region != null and region != \"\" '> ",
            "and o.receiver_city = #{region} ",
            "</if> ",

            "and o.order_time BETWEEN #{startM} and #{endM}  ",
            "   ",
            "</where> ",
            "GROUP BY ",
            "o.user_record_id ",
            "</script> ",
    })
    List<UserDto> findCityOrderRecord(@Param("province") String province,
                                      @Param("region") String region,
                                      @Param("startM") LocalDateTime startM,
                                      @Param("endM") LocalDateTime endM);


    @Select({
            "<script>",
            "SELECT count(o.id) totalOrder ,o.user_record_id userRecordId ",
            "FROM sd_order o ",
            "where  o.order_time BETWEEN #{startMonth} and #{endMonth} and  o.user_record_id is not null and o.tool_man_id is not null  and  o.user_record_id  in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "  and o.order_state in(2,3,12)   and  o.on_cancel = 0 ",
            "GROUP BY o.user_record_id  ",
            "</script>",
    })
    List<UserDto> findCityOrderRates(@Param("ids") List<Long> userRecordIds,
                                     @Param("startMonth") LocalDateTime startMonth,
                                     @Param("endMonth") LocalDateTime endMonth);

    @Select({
            "<script> ",
            "SELECT  ",
            " DATE_FORMAT( su.created_at, '%Y-%m' ) AS name , ",
            " count( su.id ) AS data  ",
            "from sd_user_record su,sd_tool_man_info sd ,sys_user u ,sys_dept t ",
            "<where> 1=1 ",

            " and u.id = sd.user_id and u.dept_id = t.id ",

            "<if test='city != null and city != \"\" '> ",
            "and sd.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sd.province = #{province} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and t.name = #{teamName} ",
            "</if> ",

            " and ",

            "su.tool_man_id = sd.user_id ",
            "and  su.created_at between #{startDate} and #{endDate}",
            "</where> ",
            "GROUP BY ",
            " DATE_FORMAT( su.created_at, '%Y-%m' )  ",
            "ORDER BY ",
            "DATE_FORMAT( su.created_at, '%Y-%m' ) ASC ",
            "</script> "
    })
    List<NumberAmount> findCityNumber(@Param("province") String province,
                                      @Param("city") String region,
                                      @Param("startDate") LocalDateTime startDate,
                                      @Param("endDate") LocalDateTime endDate,
                                      @Param("teamName") String teamName);

    @Select({
            "<script>",
            " SELECT u.nickname userName ,u.id id, t.name name,u.avatar avatar ",
            " from sys_user u,sd_tool_man_info sd ,sys_dept t",
            " <where> 1=1 and u.id = sd.user_id and u.dept_id = t.id ",
            "<if test='province != null and province != \"\" '> ",
            "and sd.province = #{province} ",
            "</if> ",
            "<if test='city != null and city != \"\" '> ",
            "and sd.city = #{city} ",
            "</if> ",
            "<if test='teamName != null and teamName != \"\" '> ",
            "and t.name = #{teamName} ",
            "</if> ",
            "</where>",
            " GROUP BY u.nickname,t.name,u.id,u.avatar ",
            "</script>",
    })
    List<UserDto> findUserDital(@Param("province") String province,
                                @Param("city") String region,
                                @Param("teamName") String teamName);


    @Select({
            "<script>",
            "SELECT IFNULL(sum(o.amount),0) AS orderAmountWeek,",
            "o.tool_man_id as id ",

            "FROM sd_order o, sys_user su ",
            "WHERE o.tool_man_id=su.id  and o.order_state  in (2,3,12)  and  o.on_cancel = 0 and o.order_time BETWEEN #{startDate} and #{endDate}  ",
            " and o.tool_man_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY o.tool_man_id ",
            " order by orderAmountWeek desc ",
            "</script>"
    })
    List<UserDto> findAllMoneyWeek(@Param("ids") List<Long> collect,
                                   @Param("startDate") LocalDateTime startTime,
                                   @Param("endDate") LocalDateTime endTime);


    @Select({
            "<script>",
            "SELECT IFNULL(sum(o.amount),0) AS orderAmountWeekAll,",
            "o.tool_man_id as id ",

            "FROM sd_order o, sys_user su ",
            "WHERE o.tool_man_id=su.id  and  o.on_cancel = 0  and o.order_time  BETWEEN #{startDate} and #{endDate} ",
            " and o.tool_man_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY o.tool_man_id ",
            " order by orderAmountWeekAll",
            "</script>"
    })
    List<UserDto> findAllMoneyWeekAll(@Param("ids") List<Long> collect,
                                      @Param("startDate") LocalDateTime startTime,
                                      @Param("endDate") LocalDateTime endTime);

    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS  customerWeek,",
            "u.id as id  ",
            "FROM sd_user_record sr, sys_user u  ",
            "WHERE sr.tool_man_id = u.id   and ( sr.deleted_at IS NULL and sr.created_at BETWEEN #{startDate} and #{endDate} ) ",
            " and sr.tool_man_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY u.id ",
            " order by customerWeek desc",
            "</script>"
    })
    List<UserDto> findCustomerNew(@Param("ids") List<Long> collect,
                                  @Param("startDate") LocalDateTime startTime,
                                  @Param("endDate") LocalDateTime endTime);

    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS customer, ",
            "u.id as id  ",
            "FROM sd_user_record sr, sys_user u  ",
            "WHERE sr.tool_man_id = u.id   and ( sr.deleted_at IS NULL ) ",
            " and sr.tool_man_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY u.id ",
            " order by customer desc ",
            "</script>"
    })
    List<UserDto> findAllCustomer(@Param("ids") List<Long> collect);


    @Select({
            "<script>",
            "SELECT o.user_record_id userRecordId ,o.tool_man_id id ",
            "FROM sd_order o WHERE ",
            " o.user_record_id is not null and o.tool_man_id is not null   ",
            " and o.order_time BETWEEN #{startDate} and #{endDate} and o.tool_man_id in  ",
            "<foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY o.user_record_id ,o.tool_man_id ",
            "</script>",
    })
    List<UserDto> findOrderMoth(@Param("ids") List<Long> collect,
                                @Param("startDate") LocalDateTime startDate,
                                @Param("endDate") LocalDateTime endDate);

    @Select({
            "<script>",
            "SELECT count(o.id) totalOrder ,o.tool_man_id id ,o.user_record_id userRecordId ",
            "FROM sd_order o ",
            "where  o.order_time BETWEEN #{startDate} and #{endDate} and  o.user_record_id is not null " ,
            "and o.tool_man_id is not null   and o.order_state in(2,3,12)   and  o.on_cancel = 0  and  o.user_record_id  in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY o.tool_man_id ,o.user_record_id ",
            "</script>",
    })
    List<UserDto> findUserRate(@Param("ids") List<Long> collect,
                               @Param("startDate") LocalDateTime startDate,
                               @Param("endDate") LocalDateTime endDate);

    @Select({
            "<script>",
            "SELECT count(o.id) totalOrder ,o.tool_man_id id  ",
            "FROM sd_order o ",
            "where  o.order_time BETWEEN #{startMoth} and #{endMoth}  ",
            " and  o.user_record_id is not null and o.tool_man_id is not null  and  o.tool_man_id  in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY o.tool_man_id  ",
            "</script>",
    })
    List<UserDto> findOrderNumberNowMothAndUID(@Param("ids") List<Long> userId,
                                               @Param("startMoth") LocalDateTime startMoth,
                                               @Param("endMoth") LocalDateTime endMoth);
}
